package com.example.demo3;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextArea;
import javafx.scene.control.cell.PropertyValueFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class ZhenyiController {

    @FXML
    private TextArea showMsgTextArea;

    @FXML
    private Button zhenyiExportButton;



    @FXML
    void zhenyiExport(ActionEvent event) throws SQLException, IOException {
        Thread thread = new Thread(()->{
            try {

                Connection connection = dbConnector.connect();
                if(!connection.isClosed())
                    System.out.println("Succeeded connecting to the sqlserver database!");
                long l = System.currentTimeMillis();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"成功连接到Microsoft SQL Server 2008 !\n");
                ResultSet resultSet = null;
                String sql = "\n" +
                        "select  tjrq 体检日期,XM 姓名,\n" +
                        "--case when jianchazhuangtai.ZT='0' then '未检' when jianchazhuangtai.ZT='1' then '已检' end 体检状态,\n" +
                        "case when XB='1' then '男' when XB='0' then '女' end 性别,NL 年龄,SFZH 身份证,shengao.JG 身高,tizhong.JG 体重,bmi.JG BMI指数,\n" +
                        "xueya.JG 血压,xinlv.JG 心率,xuehongdanbai.JG 血红蛋白,baixibao.JG 白细胞,xuexiaoban.JG 血小板,zhongxingli.JG 中性粒细胞绝对值,\n" +
                        "yinxue.JG 隐血,niaodanbai.JG 尿蛋白,ALT.JG ALT,AST.JG AST,jigan.JG 肌酐,niaosu.JG 尿素,niaosuan.JG 尿酸,xindiantu.JG 心电图,\n" +
                        "xiongpian.JG 胸片,xuetang.JG 血糖,zongdanguchun.JG 总胆固醇,dimidu.JG 低密度,ganyousanzhi.JG 甘油三酯,sejue.JG 色觉,shilizuo.JG 视力左,shiliyou.JG 视力右,\n" +
                        "jzshilizuo.JG 矫正视力左,jzshiliyou.JG 矫正视力右,tingli.JG 听阈值,chaosheng.JG 超声\n" +
                        "from futian_user.TJ_TJDJB tjdjb\n" +
                        "left join futian_user.VIEW_MXJLB shengao on tjdjb.TJBH = shengao.TJBH and shengao.TJXM = '010001'\n" +
                        "left join futian_user.VIEW_MXJLB tizhong on tjdjb.TJBH = tizhong.TJBH and tizhong.TJXM = '010002'\n" +
                        "left join futian_user.VIEW_MXJLB bmi on tjdjb.TJBH = bmi.TJBH and bmi.TJXM = '010007'\n" +
                        "left join futian_user.VIEW_MXJLB xueya on tjdjb.TJBH = xueya.TJBH and xueya.TJXM = '010003'\n" +
                        "left join futian_user.VIEW_MXJLB xinlv on tjdjb.TJBH = xinlv.TJBH and xinlv.TJXM = '010004'\n" +
                        "left join futian_user.VIEW_MXJLB xuehongdanbai on tjdjb.TJBH = xuehongdanbai.TJBH and xuehongdanbai.TJXM = '320003'\n" +
                        "left join futian_user.VIEW_MXJLB baixibao on tjdjb.TJBH = baixibao.TJBH and baixibao.TJXM = '320151'\n" +
                        "left join futian_user.VIEW_MXJLB xuexiaoban on tjdjb.TJBH = xuexiaoban.TJBH and xuexiaoban.TJXM = '320009'\n" +
                        "left join futian_user.VIEW_MXJLB zhongxingli on tjdjb.TJBH = zhongxingli.TJBH and zhongxingli.TJXM = '060267'\n" +
                        "left join futian_user.VIEW_MXJLB yinxue on tjdjb.TJBH = yinxue.TJBH and yinxue.TJXM = '060335'\n" +
                        "left join futian_user.VIEW_MXJLB niaodanbai on tjdjb.TJBH = niaodanbai.TJBH and niaodanbai.TJXM = '320030'\n" +
                        "left join futian_user.VIEW_MXJLB ALT on tjdjb.TJBH = ALT.TJBH and ALT.TJXM = '060316'\n" +
                        "left join futian_user.VIEW_MXJLB AST on tjdjb.TJBH = AST.TJBH and AST.TJXM = '060317'\n" +
                        "left join futian_user.VIEW_MXJLB jigan on tjdjb.TJBH = jigan.TJBH and jigan.TJXM = '060052'\n" +
                        "left join futian_user.VIEW_MXJLB niaosu on tjdjb.TJBH = niaosu.TJBH and niaosu.TJXM = '060318'\n" +
                        "left join futian_user.VIEW_MXJLB niaosuan on tjdjb.TJBH = niaosuan.TJBH and niaosuan.TJXM = '060053'\n" +
                        "left join futian_user.VIEW_MXJLB xindiantu on tjdjb.TJBH = xindiantu.TJBH and xindiantu.TJXM = '110020'\n" +
                        "left join futian_user.VIEW_MXJLB xiongpian on tjdjb.TJBH = xiongpian.TJBH and xiongpian.TJXM = '070007'\n" +
                        "left join futian_user.VIEW_MXJLB xuetang on tjdjb.TJBH = xuetang.TJBH and xuetang.TJXM = '060136'\n" +
                        "left join futian_user.VIEW_MXJLB zongdanguchun on tjdjb.TJBH = zongdanguchun.TJBH and zongdanguchun.TJXM = '060061'\n" +
                        "left join futian_user.VIEW_MXJLB dimidu on tjdjb.TJBH = dimidu.TJBH and dimidu.TJXM = '060063'\n" +
                        "left join futian_user.VIEW_MXJLB ganyousanzhi on tjdjb.TJBH = ganyousanzhi.TJBH and ganyousanzhi.TJXM = '060060'\n" +
                        "left join futian_user.VIEW_MXJLB sejue on tjdjb.TJBH = sejue.TJBH and sejue.TJXM = '040026'\n" +
                        "left join futian_user.VIEW_MXJLB shilizuo on tjdjb.TJBH = shilizuo.TJBH and shilizuo.TJXM = '040003'\n" +
                        "left join futian_user.VIEW_MXJLB shiliyou on tjdjb.TJBH = shiliyou.TJBH and shiliyou.TJXM = '040002'\n" +
                        "left join futian_user.VIEW_MXJLB jzshilizuo on tjdjb.TJBH = jzshilizuo.TJBH and jzshilizuo.TJXM = '040005'\n" +
                        "left join futian_user.VIEW_MXJLB jzshiliyou on tjdjb.TJBH = jzshiliyou.TJBH and jzshiliyou.TJXM = '040004'\n" +
                        "left join futian_user.VIEW_MXJLB tingli on tjdjb.TJBH = tingli.TJBH and tingli.TJXM = '050014'\n" +
                        "left join futian_user.VIEW_MXJLB chaosheng on tjdjb.TJBH = chaosheng.TJBH and chaosheng.TJXM = '230067'\n" +
                        "--left join futian_user.TJ_HISJK_RYXX jianchazhuangtai on tjdjb.TJBH = jianchazhuangtai.TJBH\n" +
                        "--where DWBH in (select BH from dbo.DWST where MC like '%振宜%') \n" +
                        "where DWBH = '6262'\n" +
                        "and tjdjb.TJBH > '2213562' \n" +
                        "and DateDiff(dd,TJRQ,getdate()) = 1   ";
                try {
                    Statement statement = connection.createStatement();
                    Thread.sleep(1000);
                    System.out.println("开始查询");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"开始查询 ...  \n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"由于需要查询的数据量较大，大约需要3分钟左右，请耐心等待\n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面的《振宜当日.xlsx》，请保证此文件未被打开!\n");

                    resultSet = statement.executeQuery(sql);
                    System.out.println("查询完毕");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"查询完毕 !\n");

                } catch (SQLException e) {
                    System.out.println("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState());
                    e.printStackTrace();
                    showMsgTextArea.appendText("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState()+"\n");
                    throw new RuntimeException(e);
                }
                XSSFWorkbook workbook = new XSSFWorkbook();
                String userPath = System.getProperty("user.home");
                String desktopPath = userPath + "\\Desktop\\振宜当日.xlsx";
                System.out.println("desktop path:"+desktopPath);

                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面，路径为： "+ desktopPath +"!\n");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"输出数据到excel中 ... \n");
                FileOutputStream fileOutputStream = new FileOutputStream(desktopPath);

                XSSFSheet sheet = workbook.createSheet("sheet1");

                Row headerRow = sheet.createRow(0);
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount ; i++) {
                    Cell cell = headerRow.createCell(i-1);
                    cell.setCellValue(metaData.getColumnName(i));
                }

                int rowNum = 1;
                while (resultSet.next()){
                    Row dataRow = sheet.createRow(rowNum++);
                    for (int i = 1; i <= columnCount; i++) {
                        Cell cell = dataRow.createCell(i-1);
                        cell.setCellValue(resultSet.getString(i));
                    }
                }

                workbook.write(fileOutputStream);
                System.out.println("写入文件完毕完毕");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"写入文件完毕完毕 !\n");

                workbook.close();

                fileOutputStream.close();
                resultSet.close();
                connection.close();
                System.out.println("关闭所有数据库连接");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"关闭所有数据库连接 !\n");
            }catch (Exception e){
                System.out.println("error msg:"+e.getMessage());
                showMsgTextArea.appendText("error msg:"+e.getMessage()+"\n");
            }

        });
        thread.start();

    }


    @FXML
    public void initialize(){


    }
}